注意：所有文章除特别说明外，转载请注明出处.
剑指Offer - 数据库
[TOC]
架构
问题：如何设计一个关系型数据库。这里将整个数据库划分成两个部分：
1.存储，相当于OS的文件系统，存储数据
2.程序实例，包括存储管理/缓存机制/SQL解析模块/日志管理/权限划分/异常容灾机制/索引管理和锁管理，这些对程序进行管理。
索引
问题：索引使用的理由，快速检索数据，索引是根据字典中的索引复现出来的。
问题：什么样的信息能够成为索引，如主键等。
问题：索引的数据结构
1.生成索引，建立二叉树进行二分查找
2.生成索引，建立B-Tree结构进行查找
B-Tree定义：
1.根节点至少包括两个孩子
2.树中每个节点最多包含有m个孩子（m >= 2）
3.除根节点和叶子节点之外，其它每个节点至少有ceil(m/2)个孩子 ceil-向上取整
4.所有叶子节点位于同一层
5.节点之间的限制...
3.生成索引，建立B+-Tree结构进行查找
B+-Tree定义：
B+树是B树的变体，定义与B树相同，除了：
1.非叶子节点的子树指针与关键字个数相同
2.非叶子节点的子树指针p[i],指向关键字值[k[i], k[i+1]]的子树
3.非叶子节点仅用来索引，数据都保存在叶子节点中
4.所有叶子节点均有一个链指针指向下一个叶子节点
结论：B+树更适合用来做存储索引：1.B+树的磁盘读写代价更低。2.B+树的查询效率更加稳定。3.B+树更有利于对数据库的扫描。4.
4.生成索引，建立哈希结构进行查找
缺点：
1.仅仅能满足 = IN， 不能使用范围查询
2.无法被用来避免数据的排序操作
3.不能利用部分索引键查询
4.不能避免表扫描
5.遇到大量的hash值相等的情况后性能并不一定就会比B树索引高
5.BitMap 索引神器
问题：密集索引和稀疏索引的区别
1.密集索引文件中的每个搜索码值都对应一个索引值
2.稀疏索引文件只为索引码的某些值建立索引项
InnoDB
1.如果一个主键被定义，则该主键则作为密集索引
2.如果没有主键被定义，则该表的第一个唯一非空索引则作为密集索引
3.如果不满足上面的条件，InnoDB会生成一个隐藏的主键（密集索引）
4.非主键索引储存相关键位和其对应的主键值，包含两次查找
问题：如何定位和优化慢查询SQL
1.根据慢日志定位慢查询sql
1.在mysql中显示慢查询参数
show variables like %quer%;
2.在mysql中显示慢查询状态
show status like %slow_queries%
3.设置慢查询日志记录开关
set global slow_query_log = on;
4.设置慢查询等待时间，超出该时间则会被记录
set global long_query_time =1;(ls) 需要重新连接或者在配置文件中设置
2.使用explain等工具分析sql
关键字段：
type：在index或all就表明该语句需要优化
extra：
1.using filesort
不会用到表里的任何索引，而是借助于mysql外部的一些方式做排序，这样会远慢于所以的排序方式
2.using temporary
mysql在排序时候使用临时表，常见于order by和分组查询 group by
注意：出现上面连个需要优化sql语句
3.修改sql，或尽量让sql走索引
或者给字段加索引
4.
问题：联合索引的最左匹配原则的成因
1.最左匹配原则是一个非常重要的原则，mysql会一直向右匹配直到遇到范围查询（> | < | between | like）就停止匹配。
如：a = 3 and b = 4 and c > 5 adn d = 6
1.如果这里建立的是(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的所以则都可以用到
2.=和IN可以乱序。
如：a=1 and b = 2 and c = 3
1.建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮助我们优化成索引可以识别的形式。
成因：因为mysql主要是根据从左到右的顺序进行order的，如果左边第一个用不到的话，第二个就不会走索引
锁
数据库锁的分类
1.按锁的粒度划分
表级锁 | 行级锁 | 页级锁
2.按锁级别划分
共享锁 | 排它锁
3.加锁方式
自动锁 | 显示锁
4.操作划分
DML锁 | DDL锁
5.按使用方式划分
乐观锁 | 悲观锁
问题：MyISAM与InnoDB关于锁方面的区别
1.MyISAM默认用的是表级锁，不支持行级锁
MyISAM适合的场景：
1.频繁执行全表count语句
2.对数据进行增删改的频率不高，查询非常频繁
3.没有事务
2.InnoDB默认用的是行级锁，也支持表级锁
InnoDB适合的场景：
1.数据的增删改查都相当频繁
2.可靠性要求比较高，要求支持事务
3.
问题：数据库事务的四大特性
ACID
问题：事务隔离级别以及各级别下的并发访问问题
事务并发访问引起的问题以及如何避免
问题1：更新丢失，mysql所有事务隔离级别在数据库层面上均可避免
问题2：脏读，read-committed事务隔离级别以上可避免
问题3：不可重复读，repeatable-read事务隔离级别以上可以避免
问题4：幻读，serializable事务隔离级别可避免
问题：InnoDB可重复读隔离级别下如何避免幻读
表象：快照读（非阻塞读）伪MVCC
当前读：表示加了锁的增删改查语句
select .. lock in share mode 共享锁
select .. for update 排它锁
update
delete
insert
快照读：不加锁的非阻塞读
select
内在：next-key锁（行锁 + gap锁）
行锁：
对单个行上锁
Gap锁：
在rr级别和serialable级别下默认支持Gap锁
对主键索引或者唯一索引的时候
如果where条件全部命中，则不会用Gap锁，只会加记录锁
如果where条件部分命中或者全不命中则会加Gap锁
提示：Gap锁会用在非唯一索引或者不走索引的当前读中
问题：RC RR级别下的InnoDB的非阻塞读如何实现
1.数据行里的 DB_TRX_ID | DB_ROLL_PTR DB_ROW_ID 字段
2.UNDO日志
3.read view
语法
1.GROUP BY
1.满足SELECT子句中的列名必须为分组列或列函数
2.列函数对于GROUP BY子句定义的每个组各返回一个结果
3.如果用到GROUP BY语句，那么在SELECT语句中选出的列要么是GROUP BY里面用到的列，要么是带有如SUM MIN等列函数的列
2.HAVING
1.通常与GROUP BY 子句一起使用
2.WHERE 过滤行，HAVING过滤组
3.在同一sql中，出现的顺序是：WHERE > GROUP BY > HAVING
如：查询平均成绩大于60的同学的学号和平均成绩
SELECT student_id, AVG(score) FROM student GROUP BY student_id HAVING AVG(score)>60
提示：如果没有GROUP BY子句，那么HAVING与WHERE的作用是一样的
如：查询没有学完所有课程的同学的学号、姓名
SELECT
stu.student_id, stu.name FROM student stu, score s
WHERE
stu.student_id = s.student_id
GROUP BY
s.student_id
HAVING
count(*) < (
SELECT COUNT(*) FROM course
)
